-- **************************************************************************
--    Project Name:   基础资料-未运营网点数
--    Job Name:       jms_dm__dm_no_operate_site_num
--    Description :  基础资料主表，和揽收，派件，签收表关联
--    Author :        马诚
--    date：          2022/08/05
-- **************************************************************************
--
--
-- **************************************************************************
with taking_base as (
        select 
            t.scan_site_code,
            count(t.waybill_no) as num
        from jms_dwd.dwd_tab_barscan_taking_base_dt t
        where t.dt >= '{{ execution_date | date_add(-6) | cst_ds }}' and t.dt <= '{{ execution_date | cst_ds }}'
        group by 
            t.scan_site_code
),
deliver_base as (
        select 
            t.scan_site_code,
            count(t.waybill_no)  as num
        from jms_dwd.dwd_tab_barscan_deliver_base_dt t
        where t.dt >= '{{ execution_date | date_add(-6) | cst_ds }}' and t.dt <= '{{ execution_date | cst_ds }}'
        group by 
            t.scan_site_code
),
sign_base as (
        select 
            t.signed_site_code as scan_site_code,
            count(t.waybill_no)  as num
        from jms_dwd.dwd_tab_barscan_sign_base_dt t
        where t.dt >= '{{ execution_date | date_add(-6) | cst_ds }}' and t.dt <= '{{ execution_date | cst_ds }}'
        group by 
            t.signed_site_code
),
result_table as (
    select
         t.name as site_name
        ,t.code as site_code
        ,t.agent_code
        ,t.agent_name
        ,nvl(t1.num,0) as taking_num
        ,nvl(t2.num,0) as deliver_num
        ,nvl(t3.num,0) as sign_num
        ,t.create_time
    from jms_dim.dim_network_whole_massage t
    left join taking_base t1 on t.code=t1.scan_site_code
    left join deliver_base t2 on t.code=t2.scan_site_code
    left join sign_base t3 on t.code=t3.scan_site_code
    where t.is_enable=1 and t.network_type=6  and t.is_retrogression=2 and t.is_suspend=2 and t.function_type_id in (27,111) 
)
insert overwrite table jms_dm.dm_no_operate_site_num partition (dt)
select
 '{{ execution_date | date_add(1) | cst_ds }}' as date_time
 ,t.site_name
 ,t.site_code
 ,t.agent_name
 ,t.agent_code
 ,case when t.taking_num+t.deliver_num+sign_num=0 then 0 else 1 end  as is_operate --是否运营，1-是，0-否
 ,t.create_time
 ,'{{ execution_date | date_add(1) | cst_ds }}' as dt
from result_table t 

 distribute by pmod(hash(rand()), 3)
;
